package com.enjoyor.soa.traffic.util.helper.export;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelExportTools<T> {
    public ExcelExportTools() {
    }

    public boolean exportExcel(List<T> list, String filePath) throws Exception {
        OutputStream out = new FileOutputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFCellStyle colStyle = this.getColumsStyle(workbook);
        int maxY = 0;
        this.createCellsInSheet(list, sheet, maxY, colStyle, "yyyy-MM-dd", (String[]) (new String[0]));
        workbook.write(out);
        if (out != null) {
            out.close();
        }

        return false;
    }

    public boolean exportExcel(List<T> list, CustomHSSFCell[] headers, String filePath, String sheetTitle,
            String dateFormat, String[] unExportedFields) throws Exception {
        OutputStream out = new FileOutputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetTitle);
        HSSFCellStyle styleHeader = this.getFirstHeaderStyle(workbook);
        HSSFCellStyle colStyle = this.getColumsStyle(workbook);
        int maxY = 0;
        HSSFRow row = null;

        for (int i = 0; i < headers.length; ++i) {
            int rowBegin = headers[i].getRowBegin();
            row = sheet.getRow(rowBegin);
            if (row == null) {
                row = sheet.createRow(rowBegin);
                row.setHeight((short) 600);
            }

            HSSFCell cell = row.createCell(headers[i].getColBegin());
            sheet.autoSizeColumn(headers[i].getColBegin());
            sheet.setColumnWidth(headers[i].getColBegin(),
                    (headers[i].getColWidth() == 0 ? 20 : headers[i].getColWidth()) * 256);
            cell.setCellStyle(styleHeader);
            HSSFRichTextString text = new HSSFRichTextString((String) headers[i].getCellValue());
            cell.setCellValue(text);
            if (headers[i].isMerged()) {
                sheet.addMergedRegion(new CellRangeAddress(headers[i].getRowBegin(), headers[i].getRowEnd(),
                        headers[i].getColBegin(), headers[i].getColEnd()));
            }

            if (maxY <= headers[i].getRowEnd()) {
                maxY = headers[i].getRowEnd();
            }
        }

        this.createCellsInSheet(list, sheet, maxY + 1, colStyle, dateFormat, unExportedFields);
        workbook.write(out);
        if (out != null) {
            out.close();
        }

        return true;
    }

    public boolean exportExcel(List<T> list, CustomHSSFCell[] headers, String filePath, String sheetTitle)
            throws Exception {
        return this.exportExcel(list, headers, filePath, sheetTitle, "yyyy-MM-dd");
    }

    public boolean exportExcel(List<T> list, CustomHSSFCell[] headers, String filePath, String sheetTitle,
            String dateFormat) throws Exception {
        return this.exportExcel(list, headers, filePath, sheetTitle, dateFormat, (String[]) null);
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public void createCellsInSheet(List<T> list, HSSFSheet sheet, int rowIndex, HSSFCellStyle colStyle,
            String dateFormat, String[] unExportedFields) throws Exception {
        Iterator<T> iterator = list.iterator();
        HSSFRow row = null;
        Field[] fields = null;
        Field field = null;

        for (HSSFCell cell = null; iterator.hasNext(); ++rowIndex) {
            row = sheet.createRow(rowIndex);
            row.setHeight((short) 500);
            T t = iterator.next();
            fields = t.getClass().getDeclaredFields();

            for (int i = 0; i < fields.length; ++i) {
                cell = row.createCell(i);
                cell.setCellStyle(colStyle);
                field = fields[i];
                String fieldName = field.getName();
                if (unExportedFields != null && this.containStr(fieldName, unExportedFields)) {
                    break;
                }

                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Class tCls = t.getClass();
                Method getMethod = tCls.getMethod(getMethodName);
                Object value = getMethod.invoke(t);
                if (value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue((double) intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue((double) fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue((double) longValue);
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                        cell.setCellValue(sdf.format(date));
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }

    }

    public boolean exportExcelOfFieldName(List<T> list, CustomHSSFCell[] topheaders, CustomHSSFCell[] midheaders,
            CustomHSSFCell[] botheaders, String filePath, String sheetTitle, String dateFormat) throws Exception {
        OutputStream out = new FileOutputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetTitle);
        HSSFCellStyle styleHeader = this.getFirstHeaderStyle(workbook);
        HSSFCellStyle colStyle = this.getColumsStyle(workbook);
        int maxY = 0;
        HSSFRow row = null;
        int i;
        int rowBegin;
        HSSFCell cell;
        HSSFRichTextString text;
        if (topheaders != null) {
            for (i = 0; i < topheaders.length; ++i) {
                rowBegin = topheaders[i].getRowBegin();
                row = sheet.getRow(rowBegin);
                if (row == null) {
                    row = sheet.createRow(rowBegin);
                    row.setHeight((short) 600);
                }

                cell = row.createCell(topheaders[i].getColBegin());
                sheet.autoSizeColumn(topheaders[i].getColBegin());
                sheet.setColumnWidth(topheaders[i].getColBegin(),
                        (topheaders[i].getColWidth() == 0 ? 20 : topheaders[i].getColWidth()) * 256);
                cell.setCellStyle(styleHeader);
                text = new HSSFRichTextString((String) topheaders[i].getCellValue());
                cell.setCellValue(text);
                if (topheaders[i].isMerged()) {
                    sheet.addMergedRegion(new CellRangeAddress(topheaders[i].getRowBegin(), topheaders[i].getRowEnd(),
                            topheaders[i].getColBegin(), topheaders[i].getColEnd()));
                }

                if (maxY <= topheaders[i].getRowEnd()) {
                    maxY = topheaders[i].getRowEnd();
                }
            }
        }

        for (i = 0; i < midheaders.length; ++i) {
            rowBegin = midheaders[i].getRowBegin();
            row = sheet.getRow(rowBegin);
            if (row == null) {
                row = sheet.createRow(rowBegin);
                row.setHeight((short) 600);
            }

            cell = row.createCell(midheaders[i].getColBegin());
            sheet.autoSizeColumn(midheaders[i].getColBegin());
            sheet.setColumnWidth(midheaders[i].getColBegin(),
                    (midheaders[i].getColWidth() == 0 ? 20 : midheaders[i].getColWidth()) * 256);
            cell.setCellStyle(styleHeader);
            text = new HSSFRichTextString((String) midheaders[i].getCellValue());
            cell.setCellValue(text);
            if (midheaders[i].isMerged()) {
                sheet.addMergedRegion(new CellRangeAddress(midheaders[i].getRowBegin(), midheaders[i].getRowEnd(),
                        midheaders[i].getColBegin(), midheaders[i].getColEnd()));
            }

            if (maxY <= midheaders[i].getRowEnd()) {
                maxY = midheaders[i].getRowEnd();
            }
        }

        this.createCellsInSheet(list, sheet, maxY + 2, colStyle, dateFormat, midheaders);
        if (botheaders != null) {
            for (i = 0; i < botheaders.length; ++i) {
                rowBegin = botheaders[i].getRowBegin();
                row = sheet.getRow(rowBegin);
                if (row == null) {
                    row = sheet.createRow(rowBegin);
                    row.setHeight((short) 600);
                }

                cell = row.createCell(botheaders[i].getColBegin());
                sheet.autoSizeColumn(botheaders[i].getColBegin());
                sheet.setColumnWidth(botheaders[i].getColBegin(),
                        (botheaders[i].getColWidth() == 0 ? 20 : botheaders[i].getColWidth()) * 256);
                cell.setCellStyle(styleHeader);
                text = new HSSFRichTextString((String) botheaders[i].getCellValue());
                cell.setCellValue(text);
                if (botheaders[i].isMerged()) {
                    sheet.addMergedRegion(new CellRangeAddress(botheaders[i].getRowBegin(), botheaders[i].getRowEnd(),
                            botheaders[i].getColBegin(), botheaders[i].getColEnd()));
                }

                if (maxY <= botheaders[i].getRowEnd()) {
                    maxY = botheaders[i].getRowEnd();
                }
            }
        }

        workbook.write(out);
        if (out != null) {
            out.close();
        }

        return true;
    }

    public boolean exportExcelOfFieldName(List<T> list, CustomHSSFCell[] headers, String filePath, String sheetTitle,
            String dateFormat) throws Exception {
        File file =new File(filePath);
        if (!file.exists() && !file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        OutputStream out = new FileOutputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetTitle);
        HSSFCellStyle styleHeader = this.getFirstHeaderStyle(workbook);
        HSSFCellStyle colStyle = this.getColumsStyle(workbook);
        int maxY = 0;
        HSSFRow row = null;

        for (int i = 0; i < headers.length; ++i) {
            int rowBegin = headers[i].getRowBegin();
            row = sheet.getRow(rowBegin);
            if (row == null) {
                row = sheet.createRow(rowBegin);
                row.setHeight((short) 600);
            }

            HSSFCell cell = row.createCell(headers[i].getColBegin());
            sheet.autoSizeColumn(headers[i].getColBegin());
            sheet.setColumnWidth(headers[i].getColBegin(),
                    (headers[i].getColWidth() == 0 ? 20 : headers[i].getColWidth()) * 256);
            cell.setCellStyle(styleHeader);
            HSSFRichTextString text = new HSSFRichTextString((String) headers[i].getCellValue());
            cell.setCellValue(text);
            if (headers[i].isMerged()) {
                sheet.addMergedRegion(new CellRangeAddress(headers[i].getRowBegin(), headers[i].getRowEnd(),
                        headers[i].getColBegin(), headers[i].getColEnd()));
            }

            if (maxY <= headers[i].getRowEnd()) {
                maxY = headers[i].getRowEnd();
            }
        }

        this.createCellsInSheet(list, sheet, maxY + 1, colStyle, dateFormat, headers);
        workbook.write(out);
        if (out != null) {
            out.close();
        }
        return true;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    private void createCellsInSheet(List<T> list, HSSFSheet sheet, int rowIndex, HSSFCellStyle colStyle,
            String dateFormat, CustomHSSFCell[] headers) throws Exception {
        Iterator<T> iterator = list.iterator();
        HSSFRow row = null;
        HSSFCell cell = null;
        String fieldName = null;

        for (Object value = null; iterator.hasNext(); ++rowIndex) {
            row = sheet.createRow(rowIndex);
            row.setHeight((short) 500);
            T t = iterator.next();

            for (int i = 0; i < headers.length; ++i) {
                cell = row.createCell(i);
                cell.setCellStyle(colStyle);
                fieldName = headers[i].getFieldName();
                if (t instanceof HashMap) {
                    value = ((HashMap) t).get(fieldName);
                } else {
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName);
                    value = getMethod.invoke(t);
                }

                if (value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue((double) intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue((double) fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue((double) longValue);
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                        cell.setCellValue(sdf.format(date));
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }

                if (headers[i].getKeyValue() != null) {
                    cell.setCellValue((String) headers[i].getKeyValue().get(value.toString()));
                }
            }
        }

    }

    public boolean containStr(String fieldName, String[] unExportedFields) {
        for (int i = 0; i < unExportedFields.length; ++i) {
            if (unExportedFields[i].compareTo(fieldName) == 0) {
                return true;
            }
        }

        return false;
    }

    public HSSFCellStyle getColumsStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment((short) 2);
        style.setVerticalAlignment((short) 1);
        HSSFFont font = workbook.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        return style;
    }

    public HSSFCellStyle getFirstHeaderStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment((short) 2);
        style.setVerticalAlignment((short) 1);
        HSSFFont font = workbook.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight((short) 700);
        style.setFont(font);
        return style;
    }
}
